var util= require('util');
var mysql= require('mysql');

const HOST= '45.186.68.5';
const PORT= 3306;
const USER= 'test';
const PASS= 'test';
const DATABASE= 'test';   //数据库名
const prefix= '';           //表前缀

const db= mysql.createPool( {
    connectionLimit : 50,
    host            : HOST,
    user            : USER,
    password        : PASS,
    database        : DATABASE,
    port            : PORT,
    insecureAuth    : true,     //是否允许旧的身份验证方法连接到数据库实例.
    multipleStatements : true,  //是否允许执行多条sql语句
} );

var findOne=function (table,where,callback){ //查找一条；
    // whre is Object; {id:1};
    var _WHERE='';
    if(util.isObject(where)){
        _WHERE+='WHERE ';
        for(var k in where){
            _WHERE+=k+"='"+where[k]+"' AND ";
        }

        _WHERE=_WHERE.slice(0,-4);
    }else if(typeof where =='string'){
        _WHERE='WHERE '+where;
    }
    var sql="SELECT * FROM "+prefix+table+' '+_WHERE+' LIMIT 1';
    console.log('sql log:'+ sql);
    db.query(sql,function(err,data){
        if(err){
            console.log(err);
            callback(0);
        }else{
            callback(data[0]);
        }
    });
};


var select=function(table, other, callback){
    //查找所有
    //other: WHERE、ORDER BY、LIMIT、......
    var sql="SELECT * FROM "+ prefix+ table+ (other || "");
    console.log('sql log:'+ sql);
    db.query(sql,function(err,data){
        if(err){
            console.log(err);
            callback(0);
        }else{
            callback(data);
        }
    });
};


var insert =function(table, obj, callback){
    //insert into table() values()
    // table: string
    // obj: {username: 'guojikai', age: '55' }
    var fields='';
    var values='';
    for( var k in obj){
        fields+=k+',';
        values=values+"'"+obj[k]+"',"
    }
    fields=fields.slice(0,-1);
    values=values.slice(0,-1);
    var sql="INSERT INTO "+prefix+table+'('+fields+') VALUES('+values+')';
    console.log('sql log:'+ sql);
    db.query(sql, function(err,data){
        if(err || !data.affectedRows){
            console.log(err);
            callback(0);
        }else{
            callback(data);
        }
    });
};


/**
 sets is object；
 where is object;
 */
var update=function(table, sets, where, callback){
    // table: string
    // sets : {name: '战士', age: 23}
    // where: {id: 223}
    var _SETS='';
    var _WHERE='';

    for(var k in sets){
        _SETS+=k+"='"+sets[k]+"',";
    }
    _SETS=_SETS.slice(0,-1);
    for(var k2 in where){
        _WHERE+=k2+"='"+where[k2]+"' AND ";
    }
    _WHERE+= "1=1";
    //update table set username='admin2',age='55'   where id="5";
    var sql="UPDATE "+prefix+table+' SET '+_SETS+' WHERE '+_WHERE;
    console.log('sql log:'+ sql);
    db.query(sql,function(err,data){
        if(err || !data.affectedRows){
            console.log(err);
            callback(0);
        }else{
            callback(data);
        }
    });
};


var del=function(table,where,callback){
    var _WHERE='';
    for(var k2 in where){
        _WHERE+=k2+"='"+where[k2]+"' AND ";
    }
    _WHERE+= "1=1";
    var sql="DELETE  FROM "+prefix+table+' WHERE '+_WHERE;
    console.log('sql log:'+ sql);
    db.query(sql,function(err,data){
        if(err || !data.affectedRows){
            console.log(err);
            callback(0);
        }else{
            callback(data);
        }
    });
};


module.exports={
    db: db,
    insert: insert,
    select: select,
    findOne: findOne,
    del: del,
    update: update,
};